﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spPostDeploy_LookupTable_MsxConfigurationEnvironment]
#-- Purpose:		Define mapping of server names to environment type
#--	Last Update:	02/13/2017
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spPostDeploy_LookupTable_MsxConfigurationEnvironment]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @t TABLE (
	[cfe_id] INT NOT NULL PRIMARY KEY,
    [cfe_find_string] NVARCHAR(20) NOT NULL,
	[cfe_msx] BIT NOT NULL DEFAULT 0,
    [cfe_begins_with] BIT NOT NULL DEFAULT 1,
    [cfe_ends_with] BIT NOT NULL DEFAULT 0,
    [cfe_environment_name] NVARCHAR(30) NOT NULL DEFAULT 'Test'
	)

--- Initialize Local Variables
INSERT INTO @t
	SELECT 0, SPACE(0), 1, 0, 0, 'MSX'
	UNION SELECT 1, 'SD', 0, 1, 0, 'Test'
	UNION SELECT 2, 'ST', 0, 1, 0, 'Test'
	UNION SELECT 3, 'SQ', 0, 1, 0, 'QA'
	UNION SELECT 4, 'SS', 0, 1, 0, 'Stage'
	UNION SELECT 5, 'SP', 0, 1, 0, 'Production'
	UNION SELECT 6, '(local)', 0, 1, 0, 'Test'
	UNION SELECT 7, 'TST', 0, 0, 0, 'Test'
	UNION SELECT 8, 'TRN', 0, 0, 0, 'Test'
	UNION SELECT 9, 'DEV', 0, 0, 0, 'Test'
	UNION SELECT 10, 'TQT', 0, 0, 0, 'Test'
	UNION SELECT 11, 'TEMP', 0, 0, 0, 'Test'
	UNION SELECT 12, 'Test', 0, 0, 0, 'Test'
	UNION SELECT 13, 'Dev', 0, 0, 0, 'Test'
	UNION SELECT 14, 'Train', 0, 0, 0, 'Test'
	UNION SELECT 15, 'QA', 0, 0, 0, 'QA'
	UNION SELECT 16, 'Stage', 0, 0, 0, 'Stage'
	UNION SELECT 17, 'Staging', 0, 0, 0, 'Stage'
	UNION SELECT 18, 'STG', 0, 0, 0, 'Stage'

--- Update Changed Records
UPDATE		t
SET			[cfe_find_string] = s.[cfe_find_string],
			[cfe_begins_with] = s.[cfe_begins_with],
			[cfe_ends_with] = s.[cfe_ends_with],
			[cfe_environment_name] = s.[cfe_environment_name]
FROM		@t s
JOIN		[dbo].[tblMSX_configuration_environment] t ON t.[cfe_id] = s.[cfe_id]
			AND NOT (
				t.[cfe_find_string] = s.[cfe_find_string]
				AND t.[cfe_msx] = s.[cfe_msx]
				AND t.[cfe_begins_with] = s.[cfe_begins_with]
				AND t.[cfe_ends_with] = s.[cfe_ends_with]
				AND t.[cfe_environment_name] = s.[cfe_environment_name]
			)

--- Insert Missing Records
SET IDENTITY_INSERT [dbo].[tblMSX_configuration_environment] ON

INSERT INTO [dbo].[tblMSX_configuration_environment] ([cfe_id], [cfe_find_string], [cfe_msx], [cfe_begins_with], [cfe_ends_with], [cfe_environment_name])
	SELECT		s.[cfe_id],
				s.[cfe_find_string],
				s.[cfe_msx],
				s.[cfe_begins_with],
				s.[cfe_ends_with],
				s.[cfe_environment_name]
	FROM		@t s
	LEFT JOIN	[dbo].[tblMSX_configuration_environment] t ON t.[cfe_id] = s.[cfe_id]
	WHERE		t.[cfe_id] IS NULL

SET IDENTITY_INSERT [dbo].[tblMSX_configuration_environment] OFF

SET NOCOUNT OFF